/*
	EXEC sp_actual_vs_standard_cycle_time  '0', 0, 0, '0', '0', 0, '0', '0', '0' 

	SELECT DISTINCT LT.LOT_ID , SUM (AST_CYCLE_TIME ) AS [STANDARD CYCLE TIME],
	( DATEDIFF( D, MIN(ALH.AL_LOAD_DATE) , ISNULL( MAX( ALH.AL_DATE_OUT), MAX(ALH.TIMESTAMP) ) ) ) AS [ACT CYCLE TIME] 
	FROM LOT_TABLE LT,PROCESS_STEP PS, ASSEMBLY_LOT_HISTORY ALH,ASSEMBLY_STAGE_TABLE AST 
	WHERE LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND LT.PROCESSSTEP_ID = AST.PROCESSSTEP_ID AND 
	LT.LOT_ID = ALH.LOT_ID AND LOT_CURRENT_STATUS ='CLOSE'
	AND ALH.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM ASSEMBLY_LOT_HISTORY WHERE LOT_ID = ALH.LOT_ID )
	GROUP BY LT.LOT_ID
	ORDER BY LT.LOT_ID

	SELECT * FROM QAD_TABLE WHERE ITEM_NUMBER = '54-62-01401-512'AND WO_STATUS = 'CLOSE'
*/
ALTER proc sp_actual_vs_standard_cycle_time

@PS_TYPE_NAME 	VARCHAR(50),
@BASE_PRODUCT_ID	INT,
@PROCESSSTEP_ID 	INT,
@CARD_TYPE		VARCHAR(50),
@PACKAGE_TYPE	VARCHAR(50),
@MEMORY_SIZE		VARCHAR(50),
@DATE_TYPE		VARCHAR(20),
@FROM_DATE		VARCHAR(20),
@TO_DATE		VARCHAR(20)

as
begin

	DECLARE @FUNCTION_RESULT TABLE( 	ITEM_NUMBER VARCHAR(50), ITEM_DESC VARCHAR(50), WORK_ORDER_NO VARCHAR(50), WORK_ORDER_ID INT, QTY_COMPLETED INT,
 						QTY_REJECTED INT, QTY_OPEN INT, ORD_DATE DATETIME, REL_DATE DATETIME, DUE_DATE DATETIME, WO_STATUS VARCHAR(50),	
						ADAPTOR_DATE DATETIME, TIME_STAMP DATETIME )

	INSERT INTO @FUNCTION_RESULT SELECT * FROM FN_QAD_SEARCH_RESULTS ( @PS_TYPE_NAME ,@BASE_PRODUCT_ID,@PROCESSSTEP_ID ,@CARD_TYPE,@PACKAGE_TYPE,@MEMORY_SIZE,@DATE_TYPE,'0','0')
	--SELECT * FROM @FUNCTION_RESULT
	SELECT DISTINCT LT.LOT_ID , LT.LOT_NUMBER, 
			( SELECT ISNULL( SUM (AST_CYCLE_TIME ), 0 ) FROM ASSEMBLY_STAGE_TABLE WHERE PROCESSSTEP_ID = LT.PROCESSSTEP_ID ) AS [STANDARD CYCLE TIME],
			( DATEDIFF( D, MIN(ALH.AL_LOAD_DATE) , ISNULL( MAX( ALH.AL_DATE_OUT), MAX(ALH.TIMESTAMP) ) ) ) AS [ACT CYCLE TIME] 
	FROM 
		LOT_TABLE LT,PRODUCT P, PROCESS_STEP PS, ASSEMBLY_LOT_HISTORY ALH,@FUNCTION_RESULT fn
	WHERE 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
		AND LT.LOT_ID = ALH.LOT_ID 
		AND P.PART_NUMBER = FN.ITEM_NUMBER
		AND LT.LOT_NUMBER = FN.WORK_ORDER_NO
		AND LOT_CURRENT_STATUS ='CLOSE'
		AND ALH.TIMESTAMP = (SELECT MAX(TIMESTAMP) FROM ASSEMBLY_LOT_HISTORY WHERE LOT_ID = ALH.LOT_ID )
	GROUP BY LT.LOT_ID, LT.LOT_NUMBER,LT.PROCESSSTEP_ID
	ORDER BY LT.LOT_ID, LT.LOT_NUMBER

end
